package com.wxh.excel;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.DecimalFormat;
import java.util.*;

/**
 * @ClassName: ExcelServiceImpl
 * @Description:
 * @Author wxh
 * @Date: 2021/12/3 15:04
 * @Version V1.0.0
 * @Since 1.8
 */
@Service("excelService")
public class ExcelServiceImpl implements ExcelService {

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelServiceImpl.class);

    @Override
    public void exportToExcel(List<Map<String, Object>> paramList, HttpServletResponse response) {
        LOGGER.info("  进入 exportToExcel 方法, 参数：paramList.size()：{}   ", paramList.size());
        long startTime = System.currentTimeMillis();

        /*
          {
                "data":[
                    {
                        "compName":"组件1",
                        "cityId":"120",
                        "indexIds":"11,12"
                    },
                    {
                        "compName":"组件2",
                        "cityId":"120",
                        "indexIds":"13,14"
                    }
                ]
            }
         */

        String excelPath = "D:/excel";

        String fileName = "indexValueData";
        XSSFWorkbook workBook = new XSSFWorkbook();

        try {
            if(!CollectionUtils.isEmpty(paramList)){
                for (int j = 0; j < paramList.size(); j++) {
                    Map<String, Object> paramMap = paramList.get(j);
                    String compName = paramMap.get("compName").toString();
                    String cityId = paramMap.get("cityId").toString();
                    String indexIds = paramMap.get("indexIds").toString();

                    List<String> ch_list = new ArrayList<>(10);
                    ch_list.add("时间");
                    ch_list.add("空间维度");
                    ch_list.add("空间维度ID");

                    List<String> en_list = new ArrayList<>(10);
                    en_list.add("START_TIME");
                    en_list.add("NE_NAME");
                    en_list.add("NE_ID");


                    List<Map<String, Object>> data_list = new ArrayList<>(10);
                    Map<String, Object> dataMap1 = new HashMap<>(16);
                    dataMap1.put("START_TIME", "2021-12-03 00:00:00");
                    dataMap1.put("NE_NAME", "重庆");
                    dataMap1.put("NE_ID", "120");
                    dataMap1.put("INDEX1", "30");
                    dataMap1.put("INDEX2", "30");
                    data_list.add(dataMap1);


                    Map<String, Object> dataMap2 = new HashMap<>(16);
                    dataMap2.put("START_TIME", "2021-12-03 01:00:00");
                    dataMap2.put("NE_NAME", "重庆");
                    dataMap2.put("NE_ID", "120");
                    dataMap1.put("INDEX1", "30");
                    dataMap1.put("INDEX2", "30");
                    data_list.add(dataMap2);

                    ExcelUtils.createXSSFSheet(workBook, ch_list, en_list, data_list, j, compName);
                }
            }
            // 导出到Excel中
            ExcelUtils.exportExcelByNew2(workBook, fileName, response, excelPath);

        }catch (Exception e){
            e.printStackTrace();
            LOGGER.info("导出Excel失败, 原因是", e.getMessage());
        }


        long endTime = System.currentTimeMillis();
        LOGGER.info("导出数据到Excel, 花费时间(毫秒): {}", (endTime - startTime));
    }

    @Override
    public void importExcel(MultipartFile file, HttpServletRequest request, HttpServletResponse response) {
        LOGGER.info("  进入 importExcel 方法, 参数：无  ");
        long startTime = System.currentTimeMillis();


        String filePath = request.getSession().getServletContext().getRealPath("/");
        File tempFile = new File(filePath + file.getOriginalFilename());
        try {
            file.transferTo(tempFile);
            tempFile = tempFile.getAbsoluteFile();
            String fileName = tempFile.getAbsolutePath();
            FileInputStream fis = new FileInputStream(tempFile);
            if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
                LOGGER.error("excel文件格式不正确");
            }

            Workbook workbook = null;
            Cell cell = null;
            if (fileName.endsWith(ExcelUtils.OFFICE_EXCEL_2007)) {
                workbook = new XSSFWorkbook(fis);
            }
            if (fileName.endsWith(ExcelUtils.OFFICE_EXCEL_2003)) {
                workbook = new HSSFWorkbook(fis);
            }

            if(workbook != null){
                for (int k = 0, len = workbook.getNumberOfSheets(); k < len; k++) {
                    List<List<Object>> list = new ArrayList<>(10);

                    Sheet sheet = workbook.getSheetAt(k);
                    int firstRow = sheet.getFirstRowNum();
                    int lastRow = sheet.getLastRowNum();

                    for (int i = firstRow ; i <= lastRow; i++) {
                        Row row = sheet.getRow(i);
                        int firstCell = row.getFirstCellNum();
                        int lastCell = row.getLastCellNum();

                        List<Object> colList = new ArrayList<>(10);
                        for (int j = firstCell; j < lastCell; j++) {
                            cell = row.getCell(j);
                            String cellValue = getCellValue(cell);
                            colList.add(cellValue);
                        }
                        list.add(colList);
                    }

                    StringBuilder sql = new StringBuilder();
                    if(!CollectionUtils.isEmpty(list)){
                        List<Object> titleList = list.get(0);

                        for (int i = 1, listLen = list.size(); i < listLen; i++) {
                            List<Object> dataList = list.get(i);
                            if(!CollectionUtils.isEmpty(dataList)){
                                String statTime = dataList.get(0).toString();
                                String neName = dataList.get(1).toString();
                                String neId = dataList.get(2).toString();

                                // 查询数据 select
                                sql.delete(0, sql.length());
                                sql.append(" select * from excel_tab where neId="+ neId);
                                // 更新数据 update
                                sql.delete(0, sql.length());
                                sql.append("  update excel_tab set neName= '"+neName+"'  ");
                                sql.append("  where neId =   "+neId);
                            }
                        }
                    }
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
            LOGGER.info(" 文件IO异常, 原因 ", e.getMessage());
        }


        long endTime = System.currentTimeMillis();
        LOGGER.info("导入Excel文件, 花费时间(毫秒): {}", (endTime - startTime));
    }

    @Override
    public void downloadExcel(HttpServletRequest request, HttpServletResponse response, String fileName) {
        LOGGER.info("  进入 downloadExcel 方法, 参数：fileName：{}   ", fileName);
        String excelPath = "D:/excel";
        File file = new File(excelPath + "/" + fileName);

        byte[] buffer = new byte[1024];
        FileInputStream fis = null;
        BufferedInputStream bis = null;
        try {
            fis = new FileInputStream(file);
            bis = new BufferedInputStream(fis);
            OutputStream os = response.getOutputStream();
            int i = bis.read(buffer);
            while (i != -1) {
                os.write(buffer, 0, i);
                i = bis.read(buffer);
            }

            // 以附件方式写入输出流中，让浏览器自动下载
            response.addHeader("Content-disposition",
                    "attachment;filename=" + new String((fileName).getBytes("GBK"), "ISO8859_1") + ".xlsx");
            System.out.println("success");
        } catch (Exception e) {
            LOGGER.error("获取文件资源时异常!(" + file.getPath() + ")");
        } finally {
            if (bis != null) {
                try {
                    bis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (fis != null) {
                try {
                    fis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 获取单元格的值
     * @param cell 单元格
     * @return String
     */
    private static String getCellValue(Cell cell) {
        String resultValue = "";
        // 判空
        if (Objects.isNull(cell)) {
            return resultValue;
        }

        // 拿到单元格类型
        int cellType = cell.getCellType();
        switch (cellType) {
            // 字符串类型
            case Cell.CELL_TYPE_STRING:
                resultValue = StringUtils.isEmpty(cell.getStringCellValue()) ? "" : cell.getStringCellValue().trim();
                break;
            // 布尔类型
            case Cell.CELL_TYPE_BOOLEAN:
                resultValue = String.valueOf(cell.getBooleanCellValue());
                break;
            // 数值类型
            case Cell.CELL_TYPE_NUMERIC:
                resultValue = new DecimalFormat("#.######").format(cell.getNumericCellValue());
                break;
            // 取空串
            default:
                break;
        }
        return resultValue;
    }
}
